package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu_jiaHui on 2017/8/8.
 */
public class MonitorRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<MonitorDto> getMonitor4DT(DTRequestDto dtRequestDto,
                                          String actorIds,
                                          Integer orgId,
                                          Boolean audit,
                                          String startRoad,
                                          String endRoad,
                                          String roadName,
                                          Integer alarmBinding) throws Exception {
        String sql = "select m.*, ur.UserName as monitorCreatorName, " +
                "og1.OrgName as monitorOrgName, og2.OrgName as monitorMainOrgName,a.deviceState " +
                "from (tb_monitor m, tb_user ur, tb_org og1, tb_org og2) " +
                "LEFT JOIN tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                "where ur.UserId = m.monitorCreatorId " +
                "and og1.OrgId = m.monitorOrgId and og2.OrgId = m.monitorMainOrgId and " +
                "m.monitorRoadName like '%" + roadName + "%' and m.monitorStartRoad like '%" + startRoad + "%' " +
                "and m.monitorEndRoad like '%" + endRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and monitorIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and m.monitorMainOrgId=" + orgId + " ";
        else
            sql += "and m.monitorOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as monitorCreatorName, " +
                    "og1.OrgName as monitorOrgName, og2.OrgName as monitorMainOrgName,a.deviceState " +
                    "from (tb_monitor m, tb_user ur, tb_org og1, tb_org og2) " +
                    "LEFT JOIN tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                    "where ur.UserId = m.monitorCreatorId " +
                    "and og1.OrgId = m.monitorOrgId and og2.OrgId = m.monitorMainOrgId and " +
                    "m.monitorRoadName like '%" + roadName + "%' and m.monitorStartRoad like '%" + endRoad + "%' " +
                    "and m.monitorEndRoad like '%" + startRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.monitorMainOrgId=" + orgId + " ";
            else
                sql += "and m.monitorOrgId = " + orgId + " ";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql += " and AlarmDeviceId is NULL ";
            }
        }
        sql += "order by monitorInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getMonitor4DTCount(DTRequestDto dtRequestDto,
                                      String actorIds,
                                      Integer orgId,
                                      Boolean audit,
                                      String startRoad,
                                      String endRoad,
                                      String roadName,
                                      Integer alarmBinding) throws Exception {
        String sql1 = "select count(*) from tb_monitor m " +
                "where m.monitorRoadName like '%" + roadName + "%' and m.monitorStartRoad like '%" + startRoad + "%' " +
                "and m.monitorEndRoad like '%" + endRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql1 += "and monitorIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql1 += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql1 += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql1 += "";
        else if (actorIds.indexOf('7') >= 0)
            sql1 += "and m.monitorMainOrgId=" + orgId + " ";
        else
            sql1 += "and m.monitorOrgId = " + orgId + " ";

        Query query1 = em.createNativeQuery(sql1);
        Integer count1 = Integer.parseInt(query1.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) from tb_monitor m " +
                    "where m.monitorRoadName like '%" + roadName + "%' and m.monitorStartRoad like '%" + endRoad + "%' " +
                    "and m.monitorEndRoad like '%" + startRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql1 += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql1 += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('7') >= 0)
                sql2 += "and m.monitorMainOrgId=" + orgId + " ";
            else
                sql2 += "and m.monitorOrgId = " + orgId + " ";
            Query query2 = em.createNativeQuery(sql2);
            count2 = Integer.parseInt(query2.getSingleResult().toString());
        }
        return count2 + count1;
    }

    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception {
        String sql = "select  MonitorStartRoad as roadName from tb_monitor where MonitorRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        sql += "union select MonitorEndRoad as roadName from tb_monitor where MonitorRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getEndRoadByStartRoad(String city, String monitorRoadName, String startRoad, String actorIds, Integer orgId) throws Exception {
        String sql = "select MonitorStartRoad as roadName from tb_monitor where MonitorRoadName like '%" + monitorRoadName + "%'" +
                " and  MonitorEndRoad like '%" + startRoad + "%' and MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        sql += "union select MonitorEndRoad as roadName from tb_monitor where MonitorRoadName like '%" + monitorRoadName + "%'" +
                " and  MonitorStartRoad like '%" + startRoad + "%' and MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<MonitorDto> getMonitor4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName, String alarmType) throws Exception {
        String sql = "select m.*, ur.UserName as monitorCreatorName, " +
                "og1.OrgName as monitorOrgName, og2.OrgName as monitorMainOrgName,a.deviceState " +
                "from (tb_monitor m, tb_user ur, tb_org og1, tb_org og2) " +
                "LEFT JOIN tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                "where ur.UserId = m.MonitorCreatorId " +
                "and og1.OrgId = m.MonitorOrgId and og2.OrgId = m.MonitorMainOrgId " +
                "and m.MonitorStartRoad like '%" + startRoad + "%' and m.MonitorEndRoad like '%" + endRoad + "%' " +
                "and m.MonitorRoadName like '%" + roadName + "%' and m.MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and m.MonitorMainOrgId=" + orgId + " ";
        else
            sql += "and m.MonitorOrgId = " + orgId + " ";
        if (alarmType.contains("0"))
            sql += " and m.AlarmDeviceId is NULL ";
        else if (alarmType.contains("1"))
            sql += " and m.AlarmDeviceId is not NULL ";
        else
            sql += "";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as monitorCreatorName," +
                    "og1.OrgName as monitorOrgName, og2.OrgName as monitorMainOrgName,a.deviceState " +
                    "from (tb_monitor m, tb_user ur, tb_org og1, tb_org og2) " +
                    "LEFT JOIN tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                    "where ur.UserId = m.MonitorCreatorId " +
                    "and og1.OrgId = m.MonitorOrgId and og2.OrgId = m.MonitorMainOrgId " +
                    "and m.MonitorStartRoad like '%" + endRoad + "%' and m.MonitorEndRoad like '%" + startRoad + "%' " +
                    "and m.MonitorRoadName like '%" + roadName + "%' and m.MonitorCity like '%" + city + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.MonitorMainOrgId=" + orgId + " ";
            else
                sql += "and m.MonitorOrgId = " + orgId + " ";
            if (alarmType.contains("0"))
                sql += " and m.AlarmDeviceId is NULL ";
            else if (alarmType.contains("1"))
                sql += " and m.AlarmDeviceId is not NULL ";
            else
                sql += "";
        }
        sql += "order by concat(MonitorInstallTime, monitorId) desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }


    public List<MonitorFaultDto> getMonitorCoordinate4Map(Integer orgId, Integer actorId) throws Exception {
        String sql = "SELECT m.MonitorId,m.MonitorLongitude,m.MonitorLatitude,m.MonitorPhoto\n" +
                "FROM tb_monitor m WHERE MonitorIsComplete=1 ";
        if (actorId != 1) {
            sql += "  and m.MonitorOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorFaultDto.class));
        return query.getResultList();
    }

    public List<DTMonitorFaultDto> getMonitorFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String roadName, String startRoad, String endRoad) throws Exception {
        String sql = "SELECT m.MonitorId,m.MonitorPhoto,m.MonitorProvince,m.MonitorCity,m.MonitorArea," +
                "m.monitorPosition,o.OrgName AS monitorOrgName,u.UserName AS monitorCreatorName," +
                "m.MonitorInstallTime\n" +
                "FROM ((tb_monitor m  JOIN tb_org o ON o.OrgId=m.MonitorOrgId)\n" +
                "JOIN tb_user u ON u.UserId=m.MonitorCreatorId)\n" +
                "WHERE m.MonitorRoadName LIKE '%" + roadName + "%' AND" +
                " m.MonitorStartRoad LIKE '%" + startRoad + "%' AND m.MonitorEndRoad LIKE '%" + endRoad + "%'";
        if (actorId != 1) {
            sql += " and m.MonitorOrgId=" + orgId + "";
        }
        if (startRoad != "" || endRoad != "") {
            sql += " UNION SELECT m.MonitorId,m.MonitorPhoto,m.MonitorProvince,m.MonitorCity,m.MonitorArea," +
                    "m.monitorPosition,o.OrgName AS monitorOrgName,u.UserName AS monitorCreatorName," +
                    "m.MonitorInstallTime\n" +
                    "FROM ((tb_monitor m  JOIN tb_org o ON o.OrgId=m.MonitorOrgId)\n" +
                    "JOIN tb_user u ON u.UserId=m.MonitorCreatorId)\n" +
                    "WHERE m.MonitorRoadName LIKE '%" + roadName + "%' AND" +
                    " m.MonitorStartRoad LIKE '%" + endRoad + "%' AND m.MonitorEndRoad LIKE '%" + startRoad + "%'";
        }
        if (actorId != 1) {
            sql += " and m.MonitorOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMonitorFaultDto.class));
        List<DTMonitorFaultDto> monitorFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return monitorFaultDtos;
    }

    public List<MonitorDto> getMonitorById(Integer monitorId) throws Exception {
        String sql = "select se.* ,o1.orgName as monitorOrgName,o2.OrgName as monitorMainOrgName," +
                " u.userName as monitorCreatorName from tb_monitor se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.monitorOrgId and o2.orgId=se.monitorMainOrgId and u.UserId=se.monitorCreatorId" +
                " and se.monitorId=" + monitorId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorDto.class));
        return query.getResultList();
    }

    public List<String> getCity4Monitor4App(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT MonitorCity from tb_monitor ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " where MonitorMainOrgId=" + orgId + " ";
        else
            sql += " where MonitorOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getRoadName4MonitorByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT MonitorRoadName from  tb_monitor where MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and MonitorMainOrgId=" + orgId + " ";
        else
            sql += " and MonitorOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getEndRoadByStartRoad4App(String city, String monitorRoadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize) {
        String sql = "select MonitorStartRoad as roadName from tb_monitor where MonitorRoadName like '%" + monitorRoadName + "%'" +
                " and  MonitorEndRoad like '%" + startRoad + "%' and MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        sql += "union select MonitorEndRoad as roadName from tb_monitor where MonitorRoadName like '%" + monitorRoadName + "%'" +
                " and  MonitorStartRoad like '%" + startRoad + "%' and MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select  MonitorStartRoad as roadName from tb_monitor where MonitorRoadName like '%" + roadName + "%' " +
                "and MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        sql += "union select MonitorEndRoad as roadName from tb_monitor where MonitorRoadName like '%" + roadName + "%' " +
                "and MonitorCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<MonitorDto> getMonitor4Map4App(Integer orgId,
                                               String actorIds,
                                               double neLongitude,
                                               double neLatitude,
                                               double swLongitude,
                                               double swLatitude) throws Exception {
        String sql = "select * from tb_monitor ";
        sql += "where monitorIsComplete = 1 " +
                "and monitorLongitude < :neLongitude and monitorLongitude > :swLongitude " +
                "and monitorLatitude < :neLatitude and monitorLatitude > :swLatitude ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and monitorMainOrgId=" + orgId + " ";
        else
            sql += "and monitorOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorDto.class));
        return query.getResultList();
    }

    public List<MonitorDto> getMonitorCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "select MonitorId, MonitorLongitude as longitude ,MonitorLatitude as latitude from tb_monitor " +
                "";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "where monitorOrgId=" + orgId + " ";
            else if (orgType == 1)
                sql += "where monitorMainOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorDto.class));
        return query.getResultList();
    }


    public List<MonitorDto> getDeviceInfo(Integer deviceId) throws Exception {
        String sql = "select tm.MonitorId,tm.MonitorProvince,tm.MonitorCity,tm.MonitorArea,tm.MonitorRoadName,tm.MonitorStartRoad,tm.MonitorEndRoad,tm.MonitorPhoto," +
                "tm.MonitorOrgId,tm.MonitorMainOrgId,tm.AlarmDeviceId,o1.OrgName as monitorOrgName,o2.OrgName as monitorMainOrgName,tm.ProjectNumber,tm.MonitorLongitude," +
                "tm.MonitorLatitude,tm.MonitorDirection,tm.MonitorPoleShape,tm.MonitorPoleSize,tm.MonitorBrand,tm.MonitorPosition,tm.MonitorSize,tm.MonitorInstallOrg," +
                "tm.MonitorInstallTime,tm.MonitorPeriod,tm.MonitorServiceLife,tm.MonitorRemark,tm.MonitorCreatorId,tm.MonitorModifierId,tm.MonitorIsComplete," +
                "tm.MonitorRegionId,tm.MonitorADeviceId,tm.AlarmCount " +
                "from tb_monitor tm,tb_org o1,tb_org o2 where tm.MonitorOrgId = o1.OrgId and tm.MonitorMainOrgId = o2.OrgId and tm.MonitorId = " + deviceId + "";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MonitorDto.class));
        return query.getResultList();
    }

}
